package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Family;
import model.Genus;
import interfaces.IGenus;

public class CGenus implements IGenus {
  private CConnection db;
	
  public CGenus(CConnection db) {
	this.db = db;
  }
  
  @Override
  public boolean create(Genus genus) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("insert into genus (name, description, observations, familyName) values ('"+genus.getName()+"', '"+genus.getDescription()+"',  '"+genus.getObservations()+"', '"+genus.getFamily().getName()+"')");
	return i > 0;
  }
  
  @Override
  public boolean update(Genus genus) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("UPDATE genus SET observations='"+genus.getObservations()+"', description='"+genus.getDescription()+"', familyName='"+genus.getFamily().getName()+"' WHERE name='"+genus.getName()+"'");
	return i > 0;
  }
	
  @Override
  public boolean delete(Genus genus) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("delete from genus where name='"+genus.getName()+"'");
	return i > 0;
  }
	
  @Override
  public ArrayList<Genus> retrieveByName(String name) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	ResultSet res = s.executeQuery("select * from genus where name = '" + name + "'");
	
	ArrayList<Genus> genus = new ArrayList<Genus>();
		
	while(res.next()) {
	  Genus g = new Genus();
	  g.setName(res.getString("name"));
	  g.setDescription(res.getString("Description"));
	  g.setObservations(res.getString("Observations"));
	  Family family = new Family();
	  family.setName(res.getString("familyName"));
	  g.setFamily(family);
	  genus.add(g);
	}
			
	return genus;
  }
	
  @Override
  public ArrayList<Genus> retrieveAll() throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	ResultSet res = s.executeQuery("select * from genus");
		
	ArrayList<Genus> genus = new ArrayList<Genus>();
	
	while(res.next()) {
	  Genus g = new Genus();
	  g.setName(res.getString("name"));
	  g.setDescription(res.getString("Description"));
	  g.setObservations(res.getString("Observations"));
	  Family family = new Family();
	  family.setName(res.getString("familyName"));
	  g.setFamily(family);
	  genus.add(g);
	}
		
	return genus;
  }
  
  public ArrayList<Object[]> countGroupingByFamily() throws SQLException, ClassNotFoundException {
		Statement s = db.getStatement();
		ResultSet res = s.executeQuery("select G.FAMILYNAME, Count(*) as count from genus G group by G.FAMILYNAME order by count DESC");
			
		ArrayList<Object[]> family = new ArrayList<Object[]>();
		
		while(res.next()) 
		{
			Object[] obj = new Object[2];
			obj[0] = res.getString("familyname");
			obj[1] = res.getInt("count");
			family.add(obj);
		}
			
		return family;
	  }
}